﻿Imports DTO
Imports System.Data.OleDb
Public Class PhieuRutTienDAO
    Public Function ThemPhieuRutTien(ByVal prtDTO As PhieuRutTienDTO) As Integer
        Dim sql As String = "Insert Into PhieuRutTien(MaSo,MaKhachHang,NgayRut,SoTienRut) Values(@MaSo,@MaKH,@NgayRut,@SoTienRut)"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@MaSo", OleDbType.Integer)
        para.Value = prtDTO.MaSo

        para = cmd.Parameters.Add("@MaKH", OleDbType.Integer)
        para.Value = prtDTO.MaKH

        para = cmd.Parameters.Add("@NhayRut", OleDbType.Date)
        para.Value = prtDTO.NgayRut.Date

        para = cmd.Parameters.Add("@SoTienRut", OleDbType.Double)
        para.Value = prtDTO.SoTienRut

        Dim kq As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If kq >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    'Lấy mã phieu rut max
    Public Function LayMaPhieuRutMax() As Integer
        Dim sql As String = "Select Max(MaPhieuRut) From PhieuRutTien"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim mapr As Integer
        Try
            mapr = cmd.ExecuteScalar
        Catch ex As Exception
            mapr = 0
        End Try

        con.Close()
        Return mapr
    End Function
    'Tìm kiếm theo nhiều tiêu trí
    Public Function BuildQuery(ByVal prtCrt As PhieuRutTienCrt, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKMaSo = " 1=1 "
        Dim strDKTenKH = " 1=1 "
        Dim strDKSoTienRut = " 1=1 "
        Dim strDKNgayRut = " 1=1 "

        If (prtCrt.MaSo <> 0) Then
            strDKMaSo = " PRT.MaSo = @MaSo "
            cmd.Parameters.Add("@MaSo", OleDbType.Integer)
            cmd.Parameters("@MaSo").Value = prtCrt.MaSo
        End If
        If (prtCrt.ChkTenKH) Then
            strDKTenKH = " KH.TenKhachHang like @TenKhachHang "
            cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            cmd.Parameters("@TenKhachHang").Value = "%" + prtCrt.TenKH + "%"
        End If
        If (prtCrt.ChkNgayRut) Then
            strDKSoTienRut = " PRT.NgayRut between @NgayRutTu and @NgayRutDen "
            cmd.Parameters.Add("@NgayRutTu", OleDbType.Date)
            cmd.Parameters("@NgayRutTu").Value = prtCrt.NgayRutTu
            cmd.Parameters.Add("@NgayRutDen", OleDbType.Date)
            cmd.Parameters("@NgayRutDen").Value = prtCrt.NgayRutDen

        End If
        If (prtCrt.SoTienRutTu) Then
            strDKSoTienRut = " PGT.SoTienRut between @SoTienRutTu and @SoTienRutDen "
            cmd.Parameters.Add("@SoTienRutTu", OleDbType.Double)
            cmd.Parameters("@SoTienRutTu").Value = prtCrt.SoTienRutTu
            cmd.Parameters.Add("@SoTienRutDen", OleDbType.Double)
            cmd.Parameters("@SoTienRutDen").Value = prtCrt.SoTienRutDen

        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += "PRT.MaSo=STK.MaSo and STK.MaKhachHang=KH.MaKhachHang and "
        strDKWhere += strDKMaSo
        strDKWhere += " and " + strDKTenKH
        strDKWhere += " and " + strDKSoTienRut
        strDKWhere += " and " + strDKNgayRut

        Dim strSQL As String = "Select PRT.MaSo,KH.TenKhachHang,PRT.SoTienRut,PRT.NgayRut From PhieuRutTien PRT,SoTietKiem STK,KhachHang KH "
        strSQL += strDKWhere
        strSQL += " Order by KH.TenKhachHang "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function
    'Goi ham tim kiem
    Public Function TimKiem(ByVal prtCrt As PhieuRutTienCrt) As List(Of PhieuRutTienCrt)
        Dim dt As New DataTable()
        Dim con As OleDbConnection
        con = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(prtCrt, con)

        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim dsprt As New List(Of PhieuRutTienCrt)()
        While (reader.Read())
            Dim _prtCrt As New PhieuRutTienCrt()
            _prtCrt.MaSo = reader.GetInt32(0)
            _prtCrt.TenKH = reader.GetString(1)
            _prtCrt.SoTienRut = reader.GetDouble(2)
            _prtCrt.NgayRut = reader.GetDateTime(3)
            dsprt.Add(_prtCrt)
        End While
        con.Close()
        Return dsprt
    End Function
    'Lấy thông tin phiếu rút tiền theo ma phieu rút
    Public Function LayThongTinPhieuRut(ByVal maphieurut As Integer) As DataTable
        Dim dt As New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT PRT.MaSo,PRT.NgayRut,PRT.SoTienRut,KH.TenKhachHang From PhieuRutTien PRT, SoTietKiem STK, KhachHang KH Where PRT.MaSo=STK.MaSo And STK.MaKhachHang=KH.MaKhachHang And PRT.MaSo=" + maphieurut.ToString()
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim Adapter As New OleDbDataAdapter()
        Adapter.SelectCommand = comand
        Adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function
    'Ham cap nhat phieu goi tien
    Public Function CapNhatPhieuRutTien(ByVal prtDTO As PhieuRutTienDTO) As Integer
        Dim sql As String = "Update PHIEURUTTIEN Set MaSo=?,NgayRut=?,SoTienRut=? Where MaPhieuRut=" + prtDTO.MaPhieuRut.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@MaSo", OleDbType.Integer)
        para.Value = prtDTO.MaSo

        para = cmd.Parameters.Add("@NgayRut", OleDbType.DBDate)
        para.Value = prtDTO.NgayRut

        para = cmd.Parameters.Add("@SoTienRut", OleDbType.Double)
        para.Value = prtDTO.SoTienRut

        Dim i As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If i >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function

    Public Function LayThongTinDanhSachPhieuRutTien() As DataTable
        Dim dt As New DataTable()
        Dim strSQl As String = "select pr.MaPhieuRut,pr.MaSo,kh.MaKhachHang,kh.TenKhachHang,pr.SoTienRut,pr.NgayRut "
        strSQl += " FROM SOTIETKIEM tk,PHIEURUTTIEN pr,KHACHHANG kh"
        strSQl += "  WHERE tk.MASO = pr.MASO And kh.MAKHACHHANG = tk.MAKHACHHANG"
        strSQl += " GROUP BY pr.MaPhieuRut,pr.MaSo,kh.MaKhachHang,kh.TenKhachHang,pr.SoTienRut,pr.NgayRut "
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim Adapter As New OleDbDataAdapter(strSQl, cnn)
        Adapter.Fill(dt)
        Return dt
    End Function

    Public Function ImportDanhSachTuExcel(ByVal str As String) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.KetNoiExcel(str)
        Dim strSql As String
        strSql = "select * from [Sheet1$]"
        Dim comand As New OleDbCommand(strSql, cnn)
        Dim adapter As New OleDbDataAdapter(comand)
        adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function

    Public Function TongChi(ByVal _date As Date) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL = "SELECT ltk.MaLoaiTietKiem,Sum(prt.SoTienRut) as TongChi FROM PhieuRutTien prt,SoTietKiem stk,LoaiTietKiem ltk"
        strSQL += " WHERE prt.MaSo = stk.MaSO and ltk.MaLoaiTietKiem = stk.MaloaiTietKiem and ltk.MaTinhTrangLoaiTietKiem = 1"
        strSQL += " And prt.NgayRut = @ngay"
        strSQL += " Group By ltk.MaLoaiTietKiem"
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim para As OleDbParameter
        para = comand.Parameters.Add("@ngay", OleDbType.DBDate)
        para.Value = _date.Date
        Dim adapter As New OleDbDataAdapter(comand)
        adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function
End Class
